Skip Headers

Oracle® Database Security Guide 10g Release 2 (10.2) Part Number B14266-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 12 Configuring and Administering Auditing

Auditing is always about accountability, and is frequently done to protect and preserve privacy for the information stored in databases. Concern about privacy policies and practices has been rising steadily with the ubiquitous use of databases in businesses and on the Internet. Oracle Database provides a depth of auditing that readily enables system administrators to implement enhanced protections, early detection of suspicious activities, and finely-tuned security responses.

The types of auditing available in Oracle Database are described in Chapter 8, "Database Auditing: Security Considerations".

The present chapter explains how to choose the types of auditing you need, how to manage that auditing, and how to use the information gained, in the following sections:

- Actions Audited by Default

- Guidelines for Auditing

- What Information Is Contained in the Audit Trail?

- Managing the Standard Audit Trail

- Viewing Database Audit Trail Information

- Fine-Grained Auditing

## 12.1 Actions Audited by Default

Regardless of whether database auditing is enabled, Oracle Database always audits certain database-related operations and writes them to the operating system audit file. This fact is called mandatory auditing, and it includes the following operations:

- Connections to the instance with administrator privileges

  An audit record is generated that lists the operating system user connecting to Oracle Database as `SYSOPER` or `SYSDBA`. This provides for accountability of users with administrative privileges. Full auditing for these users can be enabled as explained in "Auditing Administrative Users".

- Database startup

  An audit record is generated that lists the operating system user starting the instance, the user terminal identifier, and the date and time stamp. This data is stored in the operating system audit trail because the database audit trail is not available until after the startup has successfully completed.

- Database shutdown

  An audit record is generated that lists the operating system user shutting down the instance, the user terminal identifier, and the date and time stamp.

## 12.2 Guidelines for Auditing

Oracle Database 10g enables you to send audit records to the database audit trail or the operating system audit trail, when the operating system is capable of receiving them. The database audit records can also be written to operating system files in XML format. The audit trail for database administrators, for example, is typically written to a secure location in the operating system. Writing audit trails to the operating system provides a way for a separate auditor who has `root` privileges on the operating system to hold all DBAs (who don't have root access) accountable for their actions. These options, added to the broad selection of audit options and customizable triggers or stored procedures, give you the flexibility to implement an auditing scheme that suits your specific business needs.

This section describes guidelines for auditing and contains the following topics:

- Keeping Audited Information Manageable

- Auditing Normal Database Activity

- Auditing Suspicious Database Activity

- Auditing Administrative Users

- Using Triggers

- Deciding Whether to Use the Database or Operating System Audit Trail

### 12.2.1 Keeping Audited Information Manageable

Although auditing is relatively inexpensive, limit the number of audited events as far as possible. Doing so minimizes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and understand.

Use the following general guidelines when devising an auditing strategy:

- Evaluate the purpose for auditing.

  After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.

  For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing purpose might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.

- Audit knowledgeably.

  Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and consuming valuable space in the `SYSTEM` tablespace. Balance your need to gather sufficient security information with your ability to store and process it.

  For example, if you are auditing to gather information about database activity, then determine exactly what types of activities you want to track, audit only the activities of interest, and audit only for the amount of time necessary to gather the information that you desire. As another example, do not audit objects if you are only interested in logical I/O information for each session.

### 12.2.2 Auditing Normal Database Activity

When your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:

- Audit only pertinent actions.

  To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities.

- Archive audit records and purge the audit trail.

  After you have collected the required information, archive the audit records of interest and purge the audit trail of this information.

- Privacy considerations

  Privacy regulations often lead to additional business privacy policies. Most privacy laws require businesses to monitor access to personally identifiable information (PII), and such monitoring is implemented by auditing. A business-level privacy policy should address all relevant aspects of data access and user accountability, including technical, legal, and company-policy concerns.

### 12.2.3 Auditing Suspicious Database Activity

When you audit to monitor suspicious database activity, use the following guidelines:

- First audit generally, and then specifically.

  When starting to audit for suspicious database activity, it is common that not much information is available to target specific users or schema objects. Therefore, audit options must be set more generally at first. Once preliminary audit information is recorded and analyzed, the general audit options should be turned off and more specific audit options enabled. This process should continue until enough evidence is gathered to draw conclusions about the origin of the suspicious database activity.

- Protect the audit trail.

  When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited.

  See Also: "Protecting the Standard Audit Trail"

### 12.2.4 Auditing Administrative Users

Sessions for users who connect as `SYS` can be fully audited, including all users connecting as `SYSDBA` or `SYSOPER`. Use the `AUDIT_SYS_OPERATIONS` initialization parameter to specify whether such users are to be audited. For example, the following setting specifies that `SYS` is to be audited:

    AUDIT_SYS_OPERATIONS = TRUE

The default value, `FALSE`, disables `SYS` auditing.

All audit records for `SYS` are written to the operating system file that contains the audit trail, and not to `SYS.AUD$` (also viewable as `DBA_AUDIT_TRAIL`).

- In Windows, for example, when `AUDIT_TRAIL=OS`, audit records are written as events to the Event Viewer log file. If either `XML` or `XML,EXTENDED` is specified, then audit records are written in the XML format.

  Notes: `DB_UNIQUE_NAME` is defined as the globally unique name for the database in Oracle Database Reference. Refer to this book for other details and naming rules. The `adump` directory is the first default location used if the `AUDIT_FILE_DEST` `init.ora` parameter is not set or does not point to a valid directory. If writing to that first default location fails, then the `$ORACLE_HOME/rdbms/audit` directory is used as the backup default destination. If that attempt fails, then the audited operation fails and a message is written to the alert log. When `AUDIT_TRAIL=OS`, audit filenames will continue to be of the following form:
      <short_form_of_process_name>_<processid>.aud
  For example, the short process name `ora` is used for dedicated server processes, and the names `s001`, `s002`, and so on are used for shared server processes. When `AUDIT_TRAIL=XML`, the same audit filenames have the extension `xml` instead of `aud`.

- If the `AUDIT_FILE_DEST` parameter is not specified, then the default location is `$ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump` in Solaris and `$ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump` in Windows.

- For other operating systems, refer to their audit trail documentation.

All `SYS`-issued SQL statements are audited indiscriminately and regardless of the setting of the `AUDIT_TRAIL` initialization parameter.

Consider the following `SYS` session:

    CONNECT / AS SYSDBA;
    ALTER SYSTEM FLUSH SHARED_POOL;
    UPDATE salary SET base=1000 WHERE name='myname';

When `SYS` auditing is enabled, both the `ALTER SYSTEM` and `UPDATE` statements are displayed in the operating system audit file as follows:

    Thu Jan 24 12:58:00 2002
    ACTION: 'CONNECT'
    DATABASE USER: '/'
    OSPRIV: SYSDBA
    CLIENT USER: jeff
    CLIENT TERMINAL: pts/2
    STATUS: 0

    Thu Jan 24 12:58:00 2002
    ACTION: 'alter system flush shared_pool'
    DATABASE USER: ''
    OSPRIV: SYSDBA
    CLIENT USER: jeff
    CLIENT TERMINAL: pts/2
    STATUS: 0

    Thu Jan 24 12:58:00 2002
    ACTION: 'update salary set base=1000 where name='myname''
    DATABASE USER: ''
    OSPRIV: SYSDBA
    CLIENT USER: jeff
    CLIENT TERMINAL: pts/2
    STATUS: 0

Because of the superuser privileges available to users who connect as `SYSDBA`, Oracle recommends that DBAs rarely use this connection and only when necessary. Normal day to day maintenance activity can usually be done by DBAs, who are regular database users with the DBA role, or by means of a DBA role (for example, `mydba` or `jr_dba`) that your organization customizes.

### 12.2.5 Using Triggers

You can often use triggers to record additional customized information that is not automatically included in audit records, thereby customizing your own audit conditions and record contents. For example, you could define a trigger on the `EMP` table to generate an audit record whenever the salary of an employee is increased by more than 10 percent. You can include selected information, such as the values of `SALARY` before and after it was changed:

    CREATE TRIGGER audit_emp_salaries
    AFTER INSERT OR DELETE OR UPDATE ON employee_salaries
    for each row
    begin
    if (:new.salary> :old.salary * 1.10)
          then
          insert into emp_salary_audit values (
          :employee_no,
          :old.salary,
          :new.salary,
          user,
          sysdate);
          endif;
    end;

Furthermore, you can use event triggers to enable auditing options for specific users on login, and disable them upon logoff.

However, while Oracle triggers can readily monitor DML actions such as `INSERT`, `UPDATE`, and `DELETE`, monitoring on `SELECT` can be costly and, in some cases, uncertain. Triggers do not enable businesses to capture the statement executed as well as the result set from a query. They also do not enable users to define their own alert action in addition to simply inserting an audit record into the audit trail.

For these capabilities, use fine-grained auditing, which provides an extensible auditing mechanism supporting definition of key conditions for granular audit as well as an event handler to actively alert administrators to misuse of data access rights. Refer to Fine-Grained Auditing.

### 12.2.6 Deciding Whether to Use the Database or Operating System Audit Trail

The data dictionary of every Oracle database has a table named `SYS.AUD$`, commonly referred to as the database audit trail. Depending on configuration choices, this table can reside in different schema, such as the traditional `SYS` schema in the `SYSTEM` tablespace. For example, when Oracle Label Security is active, the `AUD``$` table is moved to the `SYSTEM` schema, with `SYS.AUD$` becoming a synonym to `SYSTEM.AUD$`. The database audit trail is viewable as `DBA_AUDIT_TRAIL`, and its entries arise from auditing database statements, privileges, or schema objects.

You can optionally choose to store the database audit information in an operating system file. If auditing facility of your operating system writes audit records to a file that Oracle can also write to, then you can direct the database audit entries to this file. For example, the Windows operating system allows Oracle to write audit records as events to the Application Event Log, viewable by the Event Viewer.

Consider the advantages and disadvantages of using either the database or operating system audit trail to store database audit records.

Using the database audit trail offers the following advantages:

- You can view selected portions of the audit trail with the predefined audit trail views of the data dictionary, such as `DBA_AUDIT_TRAIL`.

- You can use Oracle tools (such as Oracle Reports) or third-party tools to generate audit reports.

Using the operating system audit trail offers the following advantages:

- Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that DBAs do not have. Greater availability is another advantage to operating system storage for audit records, in that they remain available even if the database is temporarily inaccessible.

- If init.ora specifies `AUDIT_TRAIL=XML`, then audit records are written to the operating system as XML files. A new dynamic view, `V$XML_AUDIT_TRAIL`, makes such XML audit records available to DBAs through SQL query, providing enhanced usability. Querying this view causes all XML files (all files with a `.xml` extension) in the `AUDIT_FILE_DEST` directory to be parsed and presented in relational table format.

- `DBA_COMMON_AUDIT_TRAIL` also includes the standard and fine grained audit trails as written to database tables, as well as the XML-format audit trail records. The `DBA_COMMON_AUDIT_TRAIL` view includes the contents of the `V$XML_AUDIT_TRAIL` dynamic view for standard and fine-grained audit records. `DBA_COMMON_AUDIT_TRAIL` also includes the standard and fine grained audit trails as written to database tables, as well as the XML-format standard, fine grained, `SYS` and mandatory audit trails.

- Using your operating system audit trail can enable you to consolidate audit records from multiple sources, including Oracle and other applications. Examining system activity can become more efficient with all audit records in one place. Using XML audit records permits use of any standard XML editing tool to review or extract information from those records.

See Also: Your operating-system-specific documentation for information about its auditing capabilities. Audit Trail Views

## 12.3 What Information Is Contained in the Audit Trail?

Oracle Database can write records to either the database audit trail, an operating system file, or both. This section describes what information the audit trail contains.

- Database Audit Trail Contents

- Audit Information Stored in an Operating System File

### 12.3.1 Database Audit Trail Contents

The database audit trail is a single table named `SYS.AUD$` in the `SYS` schema of each Oracle Database data dictionary. Several predefined views are provided to help you use the information in this table, such as `DBA_AUDIT_TRAIL`.

Audit trail records can contain different types of information, depending on the events audited and the auditing options set. The partial list in the following section shows columns that always appear in the audit trail: if the data they represent is available, then that data populates the corresponding column. (For certain columns, this list has the column name as it displays in the audit record, shown here inside parentheses.) The operating system audit trail has only those columns marked `Yes` in the corresponding column.

Table 12-1 Audit Trail Record Data

Data Populated in Database Audit Trail | In Operating System Audit Trail?
--- | ---
(*) Bind values used for the SQL statement, if any | Footnote 1
(*) SQL text (the SQL text that triggered the auditing) | Footnote 1
Completion code of the operation | Yes
Database user name (DATABASE USER) | Yes
Date and time stamp in UTC (Coordinated Universal Time) format | No
Distinguished name | Yes
Global User unique ID | No
Instance number | No
Name of the schema object accessed | Yes
Operating system login user name (CLIENT USER) | Yes
Operation performed or attempted (ACTION) | Yes
Process number | Footnote 2
Proxy Session audit ID | No
SCN (system change number) for the SQL statement | No
Session identifier | Yes
System privileges used (PRIVILEGE) | Yes
Terminal identifier | Yes
Transaction ID | No

Footnote 1: Asterisked (*) columns in Table 12-1 appear in the audit records only if your database initialization file, `init.ora`, specifies `AUDIT_TRAIL=DB,EXTENDED` or `AUDIT_TRAIL=XML,EXTENDED`. Also, for an array, the values recorded are only the last set of bind values.

Footnote 2: Process number is populated as `ProcessId` on Unix systems. In Windows systems, the label is `ProcessId:ThreadId` (or `ProcessId` if it is not running as a thread).

Note: If the `AUDIT_TRAIL` parameter in `init.ora` is set to `XML` or `XML,EXTENDED`, then standard audit records are sent to OS files in XML format. Because XML is a standard document format, many utilities are available to parse and analyze such XML data.

If the database destination for audit records becomes full or unavailable and therefore unable to accept new records, then an audited action cannot complete. Instead, it causes an error message and is not done. In most cases, using an operating system log as the audit trail destination allows such an action to complete.

See Also: Keeping Audited Information Manageable. Controlling the Growth and Size of the Standard Audit Trail

The audit trail does not store information about any data values that might be involved in the audited statement. For example, old and new data values of updated rows are not stored when an `UPDATE` statement is audited. However, this specialized type of auditing can be performed using fine-grained auditing methods.

The `DBA_COMMON_AUDIT_TRAIL` view combines standard and fine-grained audit log records.

You can use the Flashback Query feature to show the old and new values of the updated rows, subject to any auditing policy presently in force. The current policies are enforced even if the flashback is to an old query that was originally subject to a different policy. Current business access rules always apply.

See Also: Fine-Grained Auditing for more information about methods of fine-grained auditing Flashback Queries in Oracle Database Administrator's Guide Flashback entries in the table of system privileges in Chapter 18 of Oracle Database SQL Reference

Note: To read from `FLASHBACK_TRANSACTION_TABLE` or `V$LOGMNR_CONTENTS`, the following system privilege is required: `SELECT ANY TRANSACTION`.

### 12.3.2 Audit Information Stored in an Operating System File

The operating system file that contains the audit trail can include any of the following data:

- Audit records generated by the operating system

- Database audit trail records

- Database actions that are always audited

- Audit records for administrative users (`SYS`)

Audit trail records written to an operating system audit trail may contain encoded information, but this information can be decoded using data dictionary tables and error messages as follows:

Encoded Information | How to Decode
--- | ---
Action code | Describes the operation performed or attempted, using codes listed in the `AUDIT_ACTIONS` data dictionary table, with their descriptions.
Privileges used | Describes any system privileges used to perform the operation, using codes listed in the `SYSTEM_PRIVILEGE_MAP` table, with their descriptions.
Completion code | Describes the result of the attempted operation, using codes listed in Oracle Database Error Messages, with their descriptions. Successful operations return a value of zero and unsuccessful operations return an Oracle error code corresponding to the reason the operation was unsuccessful.

## 12.4 Managing the Standard Audit Trail

This section describes various aspects of managing standard audit trail information, and contains the following topics:

- Enabling and Disabling Standard Auditing

- Standard Auditing in a Multitier Environment

- Enabling Standard Auditing Options

- Disabling Standard Audit Options

- Controlling the Growth and Size of the Standard Audit Trail

- Protecting the Standard Audit Trail

- Auditing the Standard Audit Trail

### 12.4.1 Enabling and Disabling Standard Auditing

Any authorized database user can set statement, privilege, and object auditing options at any time. However, Oracle Database does not generate audit information for the standard database audit trail unless database auditing is enabled. The security administrator is normally responsible for controlling auditing.

This section discusses the initialization parameters that enable and disable standard auditing.

Note: The initialization parameters `AUDIT_SYS_OPERATIONS` and `AUDIT_TRAIL` affecting standard auditing are static. `Static` means that if you change their values, you must shut down and restart your database for the new values to take effect. The `AUDIT_FILE_DEST` initialization parameter can be changed with `ALTER SYSTEM SET` `AUDIT_FILE_DEST = <dir> DEFERRED`, meaning the new destination will be effective for all subsequent sessions.

#### 12.4.1.1 Setting the AUDIT_TRAIL Initialization Parameter

Database auditing is enabled and disabled by the `AUDIT_TRAIL` initialization parameter in the database initialization parameter file, `init.ora`. The parameter can be set to the following values:

Parameter Value | Meaning
--- | ---
`DB` | Enables database auditing and directs all audit records to the database audit trail (`SYS.AUD$`), except for records that are always written to the operating system audit trail
`XML` | All elements of the `AuditRecord` node except `Sql_Text` and `Sql_Bind` will be printed to the operating system XML audit file.
`DB,EXTENDED` | Does all actions of `AUDIT_TRAIL=DB` and also populates the SQL bind and SQL text CLOB-type columns of the `SYS.AUD$` table, wherever possible. (These two columns are populated only when this parameter is specified.)
`XML,EXTENDED` | Does all actions of `AUDIT_TRAIL=XML` and also populates the SQL bind and SQL text CLOB-type columns of the `SYS.AUD$` table, wherever possible. (These columns are populated only when this parameter is specified.)
`OS` | Enables database auditing and directs all audit records to an operating system file
`NONE` | Disables standard auditing (This value is the default.)

Note that changes altering what objects are audited do not require restarting the database. Restart is only required if a universal change is made, such as turning on or off all auditing.

Note: You do not need to set `AUDIT_TRAIL` to enable either fine-grained auditing or `SYS` auditing. For fine-grained auditing, you simply add and remove FGA policies as you see fit, applying them to the specific operations or objects you want to monitor. You can use the `AUDIT_SYS_OPERATIONS` parameter to enable and disable `SYS` auditing.

See Also: "Auditing Administrative Users" "Fine-Grained Auditing"

#### 12.4.1.2 Specifying a Directory for the Operating System Auditing Trail

The `AUDIT_FILE_DEST` initialization parameter specifies an operating system directory into which the audit trail is written when either `AUDIT_TRAIL=OS` or `AUDIT_TRAIL=XML` is specified. Mandatory auditing information also goes into that directory, as do audit records for user `SYS` if the `AUDIT_SYS_OPERATIONS` initialization parameter is specified. `AUDIT_FILE_DEST` can be changed with `ALTER` `SYSTEM SET AUDIT_FILE_DEST = <dir> DEFERRED`, meaning the new destination will be effective for all subsequent sessions.

If the `AUDIT_FILE_DEST` parameter is not specified, then the default location on Solaris is `$ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump`, and on Windows, `$ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump`.

Notes: If your operating system supports an audit trail, then its location is operating-system-specific. For example, when the `init.ora` file contains `AUDIT_TRAIL=OS`, Windows operating systems write audit records as events to the application event log. When the `init.ora` file contains `AUDIT_TRAIL=XML` (or `XML,EXTENDED`), audit records are written to XML-formatted operating system files. Setting the `AUDIT_FILE_DEST` parameter on Windows causes the XML-format audit records to be stored in the directory specified by the parameter.

#### 12.4.1.3 Specifying the Syslog Level

To enable syslog auditing, you assign a value of OS to the `AUDIT_TRAIL` initialization parameter, as described in "Setting the AUDIT_TRAIL Initialization Parameter". You must also manually add the `AUDIT_SYSLOG_LEVEL` parameter to the database's initialization parameter file, `init.ora`. You assign to the `AUDIT_SYSLOG_LEVEL` parameter a facility and priority in the format `AUDIT_SYSLOG_LEVEL`=`facility.priority`. The `facility` argument describes the part of the operating system that is logging the message while the `priority` argument defines the severity of the message. The syslog daemon compares the value assigned to the facility argument of the `AUDIT_SYSLOG_LEVEL` parameter with the `syslog.conf` file in order to determine where to log information. For example, the following statement identifies the facility as `local1` with a priority level of `warning`:

    AUDIT_SYSLOG_LEVEL=local1.warning

Caution: You should have a strong understanding of how to work with syslog before enabling syslog auditing.

### 12.4.2 Standard Auditing in a Multitier Environment

In a multitier environment, Oracle preserves the identity of the client through all tiers, which enables auditing of actions taken on behalf of the client. To do such auditing, you use the `BY` `proxy` clause in your `AUDIT` statement.

This clause allows to do the following:

- Audit SQL statements issued by the specified proxy on its own behalf

- Audit statements executed on behalf of a specified user or users

- Audit all statements executed on behalf of any user

The following example audits `SELECT TABLE` statements issued on behalf of client `jackson` by the proxy application server `appserve`.

    AUDIT SELECT TABLE
        BY appserve ON BEHALF OF jackson;

See Also: Oracle Database Concepts andOracle Database Application Developer's Guide - Fundamentals for more information on proxies and multitier applications

### 12.4.3 Enabling Standard Auditing Options

You specify one of the four standard auditing options using the `AUDIT` statement:

Level | Effect
--- | ---
Statement | Causes auditing of specific SQL statements or groups of statements that affect a particular type of database object. For example, `AUDIT TABLE` audits the `CREATE TABLE`, `TRUNCATE TABLE`, `COMMENT ON TABLE`, and `DELETE [FROM] TABLE` statements.
Privilege | Audits SQL statements that are authorized by the specified system privilege. For example, `AUDIT CREATE ANY TRIGGER` audits statements issued using the `CREATE ANY TRIGGER` system privilege.
Object | Audits specific statements on specific objects, such as `ALTER TABLE` on the `emp` table.
Network | Audits unexpected errors in network protocol or internal errors in the network layer.

To use the `AUDIT` statement to set statement and privilege options, you must have the `AUDIT SYSTEM` privilege. To use it to set object audit options, you must own the object to be audited or have the `AUDIT ANY` privilege.

Audit statements that set statement and privilege audit options can include a `BY` clause to specify a list of users or application proxies to limit the scope of the statement and privilege audit options.

When setting auditing options, you can also specify the following conditions for auditing:

- `BY SESSION`/`BY ACCESS`

  `BY SESSION` causes Oracle Database to write a single record for all SQL statements of the same type issued in the same session. `BY ACCESS` causes Oracle to write one record for each access.

  Note: If `AUDIT_TRAIL=OS` or `AUDIT_TRAIL=XML`, then multiple records may still be written to the audit trail when `BY SESSION` is specified. Multiple records occur because while Oracle Database can write to the operating system file, the database cannot read it to detect that an audit entry already exists for the action.

- `WHENEVER SUCCESSFUL`/`WHENEVER NOT SUCCESSFUL`

  `WHENEVER SUCCESSFUL` chooses auditing only for statements that succeed. `WHENEVER NOT SUCCESSFUL` chooses auditing only for statements that fail or result in errors.

The implications of your choice of auditing options and the specification of `AUDIT` statement clauses are discussed in subsequent sections.

A new database session picks up auditing options from the data dictionary when the session is created. These auditing options remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent database sessions to use these options. Existing sessions continue using the audit options in place at session creation.

Caution: The `AUDIT` statement only specifies auditing options, it does not enable auditing as a whole. To turn auditing on and control whether Oracle Database generates audit records based on the audit options currently set, set the initialization parameter `AUDIT_TRAIL` as described in "Enabling and Disabling Standard Auditing".

See Also: Oracle Database SQL Reference for a complete description of the `AUDIT` statement

#### 12.4.3.1 Enabling Statement Auditing

Valid statement audit options that can be included in `AUDIT` and `NOAUDIT` statements are listed in the Oracle Database SQL Reference.

Two special cases of statement auditing are discussed in the following sections.

##### 12.4.3.1.1 Auditing Connections and Disconnections

The `SESSION` statement option is unique because it does not generate an audit record when a particular type of statement is issued. This option generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session is stored in a single audit record that corresponds to the session. This record can include connection time, disconnection time, and logical and physical I/O processed, among other information.

To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, `BY SESSION` (the default and only value for this option), enter the following statement:

    AUDIT SESSION;

You can set this option selectively for individual users also, as in the next example:

    AUDIT SESSION
    BY jeff, lori;

##### 12.4.3.1.2 Auditing Statements That Fail Because an Object Does Not Exist

The `NOT EXISTS` statement option specifies auditing of all SQL statements that fail because the target object does not exist.

#### 12.4.3.2 Enabling Privilege Auditing

Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the `DELETE ANY TABLE` privilege is `DELETE ANY TABLE`. To turn this option on, you use a statement similar to the following example:

    AUDIT DELETE ANY TABLE
        BY ACCESS
        WHENEVER NOT SUCCESSFUL;

Oracle Database system privileges are listed in the Oracle Database SQL Reference.

To audit all successful and unsuccessful uses of the `DELETE ANY TABLE` system privilege, enter the following statement:

    AUDIT DELETE ANY TABLE;

To audit all unsuccessful `SELECT`, `INSERT`, and `DELETE` statements on all tables and unsuccessful uses of the `EXECUTE PROCEDURE` system privilege, by all database users, and by individual audited statement, issue the following statement:

    AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
          BY ACCESS
          WHENEVER NOT SUCCESSFUL;

The `AUDIT SYSTEM` system privilege is required to set any statement or privilege audit option. Normally, the security administrator is the only user granted this system privilege.

#### 12.4.3.3 Enabling Object Auditing

The Oracle Database SQL Reference lists valid object audit options and the schema object types for which each option is available.

A user can set any object audit option for the objects contained in the schema of the user. The `AUDIT ANY` system privilege is required to set an object audit option for an object contained in another user schema or to set the default object auditing option. Normally, the security administrator is the only user granted the `AUDIT ANY` privilege.

To audit all successful and unsuccessful `DELETE` statements on the `jeff.emp` table, `BY SESSION` (the default value), enter the following statement:

    AUDIT DELETE ON jeff.emp;

To audit all successful `SELECT`, `INSERT`, and `DELETE` statements on the `dept` table owned by user `jward`, `BY ACCESS`, enter the following statement:

    AUDIT SELECT, INSERT, DELETE
         ON jward.dept
         BY ACCESS
         WHENEVER SUCCESSFUL;

To set the default object auditing options to audit all unsuccessful `SELECT` statements, `BY SESSION` (the default), enter the following statement:

    AUDIT SELECT
         ON DEFAULT
         WHENEVER NOT SUCCESSFUL;

#### 12.4.3.4 Enabling Network Auditing

Valid statement audit options that can be included in `AUDIT` and `NOAUDIT` statements are listed in the Oracle Database SQL Reference.

The errors that network auditing uncovers (such as ACTION 122 Network Error in AUDIT_ACTIONS) are not connect failures, but rather can have several possible causes. One such possible cause could be an internal event set by an Oracle engineer purely for testing purposes. Other causes include conflicting configuration settings for encryption, such as the network not finding the information required to create or process expected encryption. Table 12-2 shows four such conditions.

Table 12-2 Auditable Network Error Conditions

Error | Cause | Action
--- | --- | ---
TNS-02507 Encryption algorithm not installed | After picking an algorithm, the server was unable to find an index for it in its table of algorithms. This should be impossible because the algorithm was chosen (indirectly) from that list. | Not normally visible to the user. For further details, turn on tracing and rerun the operation. If error persists, then contact Oracle Support Services.
TNS-12648 Encryption or data integrity algorithm list empty | An Oracle Advanced Security list-of-algorithms parameter was empty. | Change the list to contain the name of at least one installed algorithm, or remove the list entirely if every installed algorithm is acceptable.
TNS-12649 Unknown encryption or data integrity algorithm | An Oracle Advanced Security list-of-algorithms parameter included an algorithm name that was not recognized. | Remove that algorithm name, correct it if it was misspelled, or install the driver for the missing algorithm.
TNS-12650 No common encryption or data integrity algorithm | The client and server have no algorithm in common for either encryption or data integrity or both. | Choose sets of algorithms that overlap. In other words, add one of the client algorithm choices to the server list or add one of the server list choices to the client algorithm.

### 12.4.4 Disabling Standard Audit Options

The `NOAUDIT` statement turns off the various audit options of Oracle Database 10g. Use it to reset statement and privilege audit options, and object audit options. A `NOAUDIT` statement that sets statement and privilege audit options can include the `BY` `user` or `BY` `proxy` option to specify a list of users to limit the scope of the statement and privilege audit options.

You can use a `NOAUDIT` statement to disable an audit option selectively using the `WHENEVER` clause. If the clause is not specified, then the auditing option is disabled entirely, for both successful and unsuccessful cases.

The `BY SESSION`/`BY ACCESS` option pair is not supported by the `NOAUDIT` statement. Audit options, no matter how they were turned on, are turned off by an appropriate `NOAUDIT` statement.

Caution: The `NOAUDIT` statement only specifies auditing options. It does not disable auditing as a whole. To turn auditing off and stop Oracle Database from generating audit records, set the initialization parameter `AUDIT_TRAIL` in the database's initialization parameter file as described in "Enabling and Disabling Standard Auditing".

See Also: Oracle Database SQL Reference for a complete syntax listing of the `NOAUDIT` statement

#### 12.4.4.1 Turning Off Statement and Privilege Auditing

The following statements turn off the corresponding audit options:

    NOAUDIT session;
    NOAUDIT session BY jeff, lori;
    NOAUDIT DELETE ANY TABLE;
    NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
        EXECUTE PROCEDURE;

The following statement turns off all statement audit options:

    NOAUDIT ALL;

The following statement turns off all privilege audit options:

    NOAUDIT ALL PRIVILEGES;

To disable statement or privilege auditing options, you must have the `AUDIT SYSTEM` system privilege.

#### 12.4.4.2 Turning Off Object Auditing

The following statements turn off the corresponding auditing options:

    NOAUDIT DELETE
       ON emp;
    NOAUDIT SELECT, INSERT, DELETE
       ON jward.dept;

Furthermore, to turn off all object audit options on the `emp` table, enter the following statement:

    NOAUDIT ALL
       ON emp;

To turn off all default object audit options, enter the following statement:

    NOAUDIT ALL
       ON DEFAULT;

All schema objects that are created before this `NOAUDIT` statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit `NOAUDIT` statement after their creation.

To disable object audit options for a specific object, you must be the owner of the schema object. To disable the object audit options of an object in another user's schema or to disable default object audit options, you must have the `AUDIT ANY` system privilege. A user with privileges to disable object audit options of an object can override the options set by any user.

#### 12.4.4.3 Turning Off Network Auditing

The following statement turns off network auditing:

    NOAUDIT NETWORK;

Recording of db link usage and login type stops.

### 12.4.5 Controlling the Growth and Size of the Standard Audit Trail

If the audit trail is full and no more audit records can be inserted, then audited statements cannot be successfully executed until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security administrator must control the growth and size of the audit trail.

When auditing is enabled and audit records are being generated, the audit trail grows according to two factors:

- The number of audit options turned on

- The frequency of execution of audited statements

To control the growth of the audit trail, you can use the following methods:

- Enable and disable database auditing. If it is enabled, then audit records are generated and stored in the audit trail. If it is disabled, then audit records are not generated.

- Be very selective about the audit options that are turned on. If more selective auditing is performed, then useless or unnecessary audit information is not generated and stored in the audit trail.

- Tightly control the ability to perform object auditing. This can be done in two different ways:

  - A security administrator owns all objects and the `AUDIT ANY` system privilege is never granted to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have `CREATE SESSION` privilege.

  - All objects are contained in schemas that do not correspond to real database users (that is, the `CREATE SESSION` privilege is not granted to the corresponding user) and the security administrator is the only user granted the `AUDIT ANY` system privilege.

  In both scenarios, object auditing is controlled entirely by the security administrator.

The maximum size of the database audit trail (`SYS.AUD$` table) is determined by the default storage parameters of the `SYSTEM` tablespace, in which it is stored.

See Also: Operating-system-specific Oracle documentation for more information about managing the operating system audit trail when directing audit records to that location

#### 12.4.5.1 Purging Audit Records from the Audit Trail

After auditing is enabled for some time, the security administrator may want to delete records from the database audit trail both to free audit trail space and to facilitate audit trail management.

For example, to delete all audit records from the audit trail, enter the following statement:

    DELETE FROM SYS.AUD$;

Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table `emp`, enter the following statement:

    DELETE FROM SYS.AUD$
         WHERE obj$name='EMP';

Note: All deletes from the audit trail are audited without exception. Refer to "Auditing the Standard Audit Trail" and "Auditing Administrative Users".

Only the user `SYS`, a user who has the `DELETE ANY TABLE` privilege, or a user to whom `SYS` has granted `DELETE` privilege on `SYS.AUD$` can delete records from the database audit trail.

Note: If the audit trail is full and connections are being audited (that is, if the `SESSION` option is set), then typical users cannot connect to the database because the associated audit record for the connection cannot be inserted into the audit trail. In this case, the security administrator must connect as `SYS` (operations by `SYS` are not audited) and make space available in the audit trail.

#### 12.4.5.2 Archiving Audit Trail Information

If audit trail information must be archived for historical purposes, then the security administrator can copy the relevant records to a normal database table (for example, using `INSERT INTO` `table` `SELECT ... FROM SYS.AUD$ ...`) or export the audit trail table to an operating system file.

See Also: Oracle Database Utilities for information about exporting tables

#### 12.4.5.3 Reducing the Size of the Audit Trail

As with any database table, after records are deleted from the database audit trail, the extents allocated for this table still exist.

If the database audit trail has many extents allocated for it, but many of them are not being used, then the space allocated to the database audit trail can be reduced by following these steps:

1. If you want to save information currently in the audit trail, then copy it to another database table or export it by using the `EXPORT` utility.

2. Connect as a user with administrator privileges.

3. Truncate `SYS.AUD$` using the `TRUNCATE` statement.

4. Reload archived audit trail records generated in Step 1.

The new version of `SYS.AUD$` is allocated only as many extents as are necessary to contain current audit trail records.

Note: `SYS.AUD$` is the only `SYS` object that should ever be directly modified.

### 12.4.6 Protecting the Standard Audit Trail

When auditing for suspicious database activity, protect the integrity of the audit trail's records to guarantee the accuracy and completeness of the auditing information.

Audit records generated as a result of object audit options set for the `SYS.AUD`$ table can only be deleted from the audit trail by someone connected with administrator privileges, which itself has protection against unauthorized use.

### 12.4.7 Auditing the Standard Audit Trail

If an application needs to give `SYS.AUD$` access to regular users (non-`SYSDBA` users), then such access needs to be audited.

To do so, you turn on the relevant auditing options for `SYS.AUD$`, which work a little differently because they are auditing actions on the audit trail(`aud$`) itself:

1. `CONNECT` `sys/``passw` `AS SYSDBA`

2. Issue the following command:

       AUDIT SELECT, INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

Please note that this command will `AUDIT` actions performed by non-`SYSDBA` users only.

If a regular user has `SELECT`, `UPDATE`, `INSERT`, and `DELETE` privileges on `SYS.AUD$` and executes a `SELECT` operation, then the audit trail will have a record of that operation. That is, `SYS.AUD$` will have a row identifying the `SELECT` action on itself, as say row1.

If a user later tries to `DELETE` this row1 from `SYS.AUD$`, then the `DELETE` will succeed, because the user has the privilege to perform this action. However, this `DELETE` action on `SYS.AUD$` is also recorded in the audit trail. Setting up this type of auditing acts as a safety feature, potentially revealing unusual or unauthorized actions. A logfile for an illustrative test case appears at the end of this chapter, at The SYS.AUD$ Auditing Table: Example.

Note: `DELETE`, `INSERT`, `UPDATE`, and `MERGE` operations on `SYS.AUD$` table are always audited, and such audit records are not allowed to be deleted.

## 12.5 Viewing Database Audit Trail Information

The database audit trail (`SYS.AUD$`) is a single table in each Oracle database data dictionary. Several predefined views are available to present auditing information from this table in a meaningful way. If you decide not to use auditing, then you can later delete these views. The following subsections show you what is in these views, how to use them, and how to delete them:

- Audit Trail Views

- Using Audit Trail Views to Investigate Suspicious Activities

- Deleting the Audit Trail Views

### 12.5.1 Audit Trail Views

The following views are created upon installation:

View | Description
--- | ---
    STMT_AUDIT_OPTION_MAP
| Contains information about auditing option type codes. Created by the `SQL.BSQ` script at `CREATE DATABASE` time.
    AUDIT_ACTIONS
| Contains descriptions for audit trail action type codes.
    ALL_DEF_AUDIT_OPTS
| Contains default object-auditing options that will be applied when objects are created.
    DBA_STMT_AUDIT_OPTS
| Describes current system auditing options across the system and by user.
    DBA_PRIV_AUDIT_OPTS
| Describes current system privileges being audited across the system and by user.
    DBA_OBJ_AUDIT_OPTS
    USER_OBJ_AUDIT_OPTS
| Describes auditing options on all objects. The `USER` view describes auditing options on all objects owned by the current user.
    DBA_AUDIT_TRAIL
    USER_AUDIT_TRAIL
| Lists all audit trail entries. The `USER` view shows audit trail entries relating to current user.
    DBA_AUDIT_OBJECT
    USER_AUDIT_OBJECT
| Contains audit trail records for all objects in the system. The `USER` view lists audit trail records for statements concerning objects that are accessible to the current user.
    DBA_AUDIT_SESSION
    USER_AUDIT_SESSION
| Lists all audit trail records concerning `CONNECT` and `DISCONNECT`. The `USER` view lists all audit trail records concerning connections and disconnections for the current user.
    DBA_AUDIT_STATEMENT
    USER_AUDIT_STATEMENT
| Lists audit trail records concerning `GRANT`, `REVOKE`, `AUDIT`, `NOAUDIT`, and `ALTER SYSTEM` statements throughout the database, or for the `USER` view, issued by the user.
    DBA_AUDIT_EXISTS
| Lists audit trail entries produced `BY AUDIT NOT EXISTS`.
    DBA_AUDIT_POLICIES
| Shows all the auditing policies on the system.
    DBA_FGA_AUDIT_TRAIL
| Lists audit trail records for value-based auditing.
    DBA_COMMON_AUDIT_TRAIL
| Combines standard and fine-grained audit log records, and includes `SYS` and mandatory audit records written in XML format.

See Also: Oracle Database Reference for detailed descriptions of the predefined views in Oracle Database

### 12.5.2 Using Audit Trail Views to Investigate Suspicious Activities

This section offers examples that demonstrate how to examine and interpret the information in the audit trail. Consider the following situation.

You would like to audit the database for the following suspicious activities:

- Passwords, tablespace settings, and quotas for some database users are altered without authorization.

- A high number of deadlocks occur, most likely because of users acquiring exclusive table locks.

- Rows are arbitrarily deleted from the `emp` table in `jeff`'s schema.

You suspect the users `jward` and `swilliams` of several of these detrimental actions.

To investigate, you issue the following statements (in the order specified):

    AUDIT ALTER, INDEX, RENAME ON DEFAULT
        BY SESSION;
    CREATE VIEW jeff.employee AS SELECT * FROM jeff.emp;
    AUDIT SESSION BY jward, swilliams;
    AUDIT ALTER USER;
    AUDIT LOCK TABLE
        BY ACCESS
        WHENEVER SUCCESSFUL;
    AUDIT DELETE ON jeff.emp
        BY ACCESS
        WHENEVER SUCCESSFUL;

The following statements are subsequently issued by the user `jward`:

    ALTER USER tsmith QUOTA 0 ON users;
    DROP USER djones;

The following statements are subsequently issued by the user `swilliams`:

    LOCK TABLE jeff.emp IN EXCLUSIVE MODE;
    DELETE FROM jeff.emp WHERE mgr = 7698;
    ALTER TABLE jeff.emp ALLOCATE EXTENT (SIZE 100K);
    CREATE INDEX jeff.ename_index ON jeff.emp (ename);
    CREATE PROCEDURE jeff.fire_employee (empid NUMBER) AS
      BEGIN
        DELETE FROM jeff.emp WHERE empno = empid;
      END;
    /

    EXECUTE jeff.fire_employee(7902);

The following sections display the information relevant to your investigation that can be viewed using the audit trail views in the data dictionary:

- Listing Active Statement Audit Options

- Listing Active Privilege Audit Options

- Listing Active Object Audit Options for Specific Objects

- Listing Default Object Audit Options

- Listing Audit Records

- Listing Audit Records for the AUDIT SESSION Option

#### 12.5.2.1 Listing Active Statement Audit Options

The following query returns all the statement audit options that are set:

    SELECT * FROM DBA_STMT_AUDIT_OPTS;

    USER_NAME               AUDIT_OPTION         SUCCESS         FAILURE
    --------------------    -------------------  ----------      ---------
    JWARD                   SESSION              BY SESSION      BY SESSION
    SWILLIAMS               SESSION              BY SESSION      BY SESSION
                            LOCK TABLE           BY ACCESS       NOT SET

Notice that the view reveals the statement audit options set, whether they are set for success or failure (or both), and whether they are set for `BY SESSION` or `BY ACCESS`.

#### 12.5.2.2 Listing Active Privilege Audit Options

The following query returns all the privilege audit options that are set:

    SELECT * FROM DBA_PRIV_AUDIT_OPTS;

    USER_NAME           PRIVILEGE            SUCCESS      FAILURE
    ------------------- -------------------- ---------   ----------
    ALTER USER          BY SESSION           BY SESSION

#### 12.5.2.3 Listing Active Object Audit Options for Specific Objects

The following query returns all audit options set for any objects with names that start with the characters `emp` and that are contained in `jeff`'s schema:

    SELECT * FROM DBA_OBJ_AUDIT_OPTS
        WHERE OWNER = 'JEFF' AND OBJECT_NAME LIKE 'EMP%';

    OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ...
    ----- ----------- --------- --- --- --- --- --- --- --- --- ...
    JEFF EMP         TABLE     S/S -/- -/- A/- -/- S/S -/- -/- ...
    JEFF EMPLOYEE    VIEW      -/- -/- -/- A/- -/- S/S -/- -/- ...

Notice that the view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:

- A dash (`-`) indicates that the audit option is not set.

- The `S` character indicates that the audit option is set, `BY SESSION`.

- The `A` character indicates that the audit option is set, `BY ACCESS`.

- Each audit option has two possible settings, `WHENEVER SUCCESSFUL` and `WHENEVER NOT SUCCESSFUL`, separated by a slash (`/`). For example, the `DELETE` audit option for `jeff.emp` is set `BY ACCESS` for successful delete statements and not set at all for unsuccessful delete statements.

#### 12.5.2.4 Listing Default Object Audit Options

The following query returns all default object audit options:

    SELECT * FROM ALL_DEF_AUDIT_OPTS;

    ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
    --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
    S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /-  -/-

Notice that the view returns information similar to the `USER_OBJ_AUDIT_OPTS` and `DBA_OBJ_AUDIT_OPTS` views (refer to previous example).

#### 12.5.2.5 Listing Audit Records

The following query lists audit records generated by statement and object audit options:

    SELECT * FROM DBA_AUDIT_OBJECT;

#### 12.5.2.6 Listing Audit Records for the AUDIT SESSION Option

The following query lists audit information corresponding to the `AUDIT SESSION` statement audit option:

    SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD,
        LOGOFF_LWRITE, LOGOFF_DLOCK
        FROM DBA_AUDIT_SESSION;

    USERNAME   LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO
    ---------- --------- ---------- ---------- ---------- ----------
    JWARD      02-AUG-91         53          2         24          0
    SWILLIAMS  02-AUG-91       3337        256        630          0

### 12.5.3 Deleting the Audit Trail Views

If you disable auditing and no longer need the audit trail views, then delete them by connecting to the database as `SYS` and running the script file `CATNOAUD.SQL`. The name and location of the `CATNOAUD.SQL` script are operating-system-dependent.

### 12.5.4 The SYS.AUD$ Auditing Table: Example

The code in this section illustrates the auditing of changes made to `SYS.AUD$`.

    SQL> @t
    SQL>
    SQL> SET FEEDBACK 1
    SQL> SET NUMWIDTH 10
    SQL> SET LINESIZE 80
    SQL> SET TRIMSPOOL ON
    SQL> SET TAB OFF
    SQL> SET PAGESIZE 100
    SQL>
    SQL> column username format a10
    SQL> column owner  format a10
    SQL> column obj_name format a6
    SQL> column action_name format a17
    SQL> SET ECHO ON
    SQL>
    SQL> connect sys/newdbapassword as sysdba
    Connected.
    SQL> grant select, insert, update, delete on sys.aud$ to jeff;

    Grant succeeded.

    SQL> grant select on dba_audit_trail to jeff;

    Grant succeeded.

    SQL> audit select, update, delete on sys.aud$ by access;

    Audit succeeded.

    SQL> truncate table sys.aud$;

    Table truncated.

    SQL>
    SQL> connect jeff/wolf
    Connected.
    SQL> select count(*) from emp

      COUNT(*)
    ----------
             0

    1 row selected.

    SQL>
    SQL> select statementid,entryid,username,action_name,returncode,owner,
      2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
      3  from dba_audit_trail
      4  order by sessionid,entryid;

    STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
    ----------- ---------- ---------- ----------------- ---------- ---------- ------
    PRIV     SES_ACTIONS
    -------- -------------------
              8          1 JEFF      SELECT                     0 SYS        AUD$

    1 row selected.

    SQL>
    SQL> update sys.aud$ set userid = 0;

    2 rows updated.

    SQL> select statementid,entryid,username,action_name,returncode,owner,
      2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
      3  from dba_audit_trail
      4  order by sessionid,entryid;

    STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
    ----------- ---------- ---------- ----------------- ---------- ---------- ------
    PRIV     SES_ACTIONS
    -------- -------------------
              8          1 0          SELECT                     0 SYS        AUD$

              9          2 0          SELECT                     0 SYS        AUD$

             10          3 JEFF       UPDATE                     0 SYS        AUD$

    3 rows selected.

    SQL>
    SQL> delete from sys.aud$;

    3 rows deleted.

    SQL> select statementid,entryid,username,action_name,returncode,owner,
      2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
      3  from dba_audit_trail
      4  order by sessionid,entryid;

    STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
    ----------- ---------- ---------- ----------------- ---------- ---------- ------
    PRIV     SES_ACTIONS
    -------- -------------------
             10          3 JEFF      UPDATE                     0 SYS        AUD$

             12          5 JEFF      DELETE                     0 SYS        AUD$

    2 rows selected.

    SQL>
    SQL> connect sys/newdbapassword as sysdba
    Connected.
    SQL> noaudit insert, select, update, delete on sys.aud$;

    Noaudit succeeded.

    SQL>
    SQL> spool off

## 12.6 Fine-Grained Auditing

As described earlier in this chapter and in Chapter 8, standard Oracle auditing is highly configurable. Its audit trail provides a fixed set of facts that monitor privileges, object access, or (optionally) SQL usage, including information about the environment or query results. The scope of standard auditing can also be substantially expanded by using triggers, and providing additional customized information.

However, two auditing goals are not directly addressed by any mechanism in standard auditing: minimizing unhelpful audits, and proving that access rights were violated. Access logs, while helpful in reconstructing events, can often be inconclusive.

Fine-grained auditing addresses these needs, taking you beyond standard auditing and enabling you to minimize false or unhelpful audits by specifying more detailed audit conditions. You do not need to set `AUDIT_TRAIL` to enable fine-grained auditing. You simply add and remove FGA policies as you see fit, applying them to the specific operations or objects you want to monitor. A built-in audit mechanism in the database prevents users from bypassing the audit. Fine-grained auditing records are stored in `SYS.FGA_LOG$` table and are accessible through the `DBA_FGA_AUDIT_TRAIL` view.

Note: The `DBA_COMMON_AUDIT_TRAIL` view combines standard and fine-grained audit log records.

See Also: To add, drop, enable, or disable policies, use The DBMS_FGA Package

### 12.6.1 Policies in Fine-Grained Auditing

Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can specify the columns and conditions that you want audit records for. Conditions can include limiting the audit to specific types of DML statements used in connection with the columns that you specify. You can also provide the name of the routine you want called when an audit event occurs. This routine can notify or alert administrators or handle errors and anomalies.

For example, most companies logically want to limit access to the specifications or test results for a product under development, and prefer that salary information remain private. Auditors need enough detail to be able to determine the data that was accessed.

Knowing only that `SELECT` privilege was used by a specific user on a particular table is not specific enough to provide accountability. A central tax authority has similar privacy concerns, needing to track access to tax returns so that employees do not snoop. Similarly, government agencies that use informants need detailed tracking of access to the database containing their identities. Such agencies also need enough detail to determine the data that was accessed, and not just information that the `SELECT` privilege was used by `JEFF` on the `TAXPAYERS` or `INFORMANTS` table.

#### 12.6.1.1 Advantages of Fine-Grained Auditing over Triggers

Fine-grained auditing meets these needs by providing functionality (and efficiency) beyond triggers. Triggers incur a PL/SQL process call for every row processed and create an audit record only when a relevant column is changed by a DML statement.

A fine-grained auditing policy, on the other hand, does not incur this cost for every row. Instead, it audits only once for every policy. Specifically, it audits when a specified relevant column occurs in a specified type of DML statement, either being changed by the statement or being in its selection criteria. This combination of criteria uncovers users who hope their information gathering will be masked because they only use the selection criteria of a DML statement. Triggers also cannot monitor the activity of another `instead-of` trigger on the same object, while fine-grained auditing supports tables and views.

#### 12.6.1.2 Extensible Interface Using Event Handler Functions

Organizations can define fine-grained auditing policies to specify the data access conditions that are to trigger audit events. These policies can use flexible event handlers that notify administrators when a triggering event has occurred. For example, an organization may allow HR clerks to access employee salary information, but trigger an audit event when salaries greater than $500K are accessed. The audit policy (where `SALARY` > 500000) is applied to the `EMPLOYEES` table through an audit policy interface (`DBMS_FGA`, a PL/SQL package).

The audit function (`handler_module`) is an alerting mechanism for the administrator. The required interface for such a function is as follows:

    PROCEDURE fname ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 )  AS ...

Where:

- `fname` is the name of the procedure

- `object_schema` is the name of the schema of the table audited

- `object_name` is the name of the table to be audited

- `policy_name` is the name of the policy being enforced

See also: The DBMS_FGA Package PL/SQL Packages and Types Reference

#### 12.6.1.3 Functions and Relevant Columns in Fine-Grained Auditing

For additional flexibility in implementation, organizations can employ a user-defined function to determine the policy condition and identify an audit column (called a relevant column) to further refine the audit policy. For example, the function could cause an audit record only when a salary greater than $250,000 is accessed.

Specifying a relevant column helps reduce the instances of false or unnecessary audit records, because the audit need only be triggered when a particular column is referenced in the query. For example, an organization may only wish to audit executive salary access when an employee name is accessed, because accessing salary information alone is not meaningful unless an HR clerk also selects the corresponding employee name. You can, however, specify that auditing occur only when all relevant columns are referenced.

If more than one relevant audit column is specified, then Oracle Database produces an audit record if the SQL statement references any of those audit columns.

The `DBMS_FGA` package administers these value-based audit policies. The security administrator creates an audit policy on the target object using the functions in the `DBMS_FGA` package.

See also: The DBMS_FGA Package (the next major section) PL/SQL Packages and Types Reference

#### 12.6.1.4 Audit Records in Fine-Grained Auditing

If any rows returned from a query block match the audit condition, then an audit event entry is inserted into the fine-grained audit trail. This entry includes user name, SQL text, bind variable, policy name, session ID, time stamp, and other attributes. Only one row of audit information is inserted into the audit trail for every FGA policy that evaluates to true. As part of the extensibility framework, administrators can also optionally define an appropriate audit event handler to process the event, for example sending an alert page to the administrator.

#### 12.6.1.5 NULL Audit Conditions

To guarantee auditing of the specified actions (`statement_types`) affecting the specified columns (`audit_column`), specify the audit_condition as `NULL` (or omit it), which is interpreted as `TRUE`. Only specifying `NULL` will guarantee auditing of the specified actions (`statement_types`) affecting the specified columns (`audit_column`). The former practice of specifying an audit condition of `1=1` to force such auditing should no longer be used and will not reliably achieve the desired result. `NULL` will cause audit even if no rows were processed, so that all actions on an `audit_column` with this policy are audited.

Note: Using an empty string is not equivalent to `NULL` and will not reliably cause auditing of all actions on a table with this policy.

The audit function is executed as an autonomous transaction, committing only the actions of the `handler_module` and not any user transaction. This function has no effect on any user SQL transaction.

If `NULL` or no audit condition is specified, then any action on a table with that policy causes an audit record to be created, whether or not rows are returned.

#### 12.6.1.6 Defining FGA Policies

The administrator uses the `DBMS_FGA.ADD_POLICY` interface to define each FGA policy for a table or view, identifying any combination of `SELECT`, `UPDATE`, `DELETE`, or `INSERT` statements. Oracle supports `MERGE` statements as well, by auditing the underlying actions of `INSERT` and `UPDATE`. To audit `MERGE`s, set up FGA on these `INSERT`s and `UPDATE`s. Only one record is generated for each policy for successful `MERGE`s.

FGA policies associated with a table or view may also specify relevant columns, so that any specified statement type affecting a particular column is audited. More than one column can be included as relevant columns in a single FGA policy. Examples include privacy-relevant columns, such as those containing social security numbers, salaries, patient diagnoses, and so on. If no relevant column is specified, then auditing applies to all columns. That is, auditing occurs whenever any specified statement type affects any column, unless you specify in the policy that auditing is to occur only when all relevant columns are referenced.

### 12.6.2 An Added Benefit to Fine-Grained Auditing

In general, fine-grained auditing policies are based on simple user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a returning row, the query is audited. Later, Oracle Database can run a user-defined audit event handler, if specified in the policy, using autonomous transactions to process the event.

Fine-grained auditing can be implemented in user applications using the `DBMS_FGA` package or by using database triggers.

The following example shows how you can audit statements (`INSERT`, `UPDATE`, `DELETE`, and `SELECT)` on table `hr.emp` to monitor any query that accesses the `salary` column of the employee records that belong to `sales` department:

    DBMS_FGA.ADD_POLICY(
    object_schema => 'hr',
    object_name   => 'emp',
    policy_name   => 'chk_hr_emp',
    audit_condition => 'dept = ''SALES'' ',
    audit_column => 'salary'
    statement_types => 'insert,update,delete,select');

Then, any of the following SQL statements will cause the database to log an audit event record.

    SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;

    SELECT salary FROM hr.emp WHERE dept = 'SALES';

    DELETE from hr.emp where salary >1000000

With all the relevant information available, and a trigger-like mechanism to use, the administrator can define what to record and how to process the audit event.

Consider the following commands:

    /* create audit event handler */
    CREATE PROCEDURE sec.log_id (schema1 varchar2, table1 varchar2, policy1 varchar2) AS
    BEGIN
    UTIL_ALERT_PAGER(schema1, table1, policy1);      -- send an alert note to my pager
    END;

    /* add the policy */
    DBMS_FGA.ADD_POLICY(
    object_schema => 'hr',
    object_name   => 'emp',
    policy_name   => 'chk_hr_emp',
    audit_condition => 'dept = ''SALES'' ',
    audit_column => 'salary',
    handler_schema => 'sec',
    handler_module => 'log_id',
    enable               =>  TRUE);

Note: Because `schema` and `table` are reserved words, they cannot be used as variables without some alteration, such as appending `1` as is done here.

After the first row of interest is fetched, the event is recorded, and the `SEC.LOG_ID` audit function is run. The audit event record generated is stored in `DBA_FGA_AUDIT_TRAIL`, which is `fga_log$` in the `SYS` schema in the `SYSTEM` tablespace. This table has reserved columns (such as `SQL_TEXT` and `SQL_BIND`) for recording SQL text, policy name, and other information. The `SQLBIND` and `SQLTEXT` values are recorded in the `LSQLTEXT` and `LSQLBIND` columns of `fga_log$` only if the policy specifies `audit_trail = DBMS_FGA.DB + DBMS_FGA.EXTENDED`. If the policy specifies `AUDIT_TRAIL=DBMS_FGA.XML`, then the audit records would be written to XML-formatted OS files.

Note: Fine-grained auditing is supported only with cost-based optimization. For queries using rule-based optimization, audit will check before applying row filtering, which could result in an unnecessary audit event trigger. Policies currently in force on an object involved in a flashback query are applied to the data returned from the specified flashback snapshot (based on time or SCN).

See Also: Oracle Database Application Developer's Guide - Fundamentals for information about using fine-grained auditing The DBMS_FGA chapter in PL/SQL Packages and Types Reference

## 12.7 The DBMS_FGA Package

The `DBMS_FGA` package provides fine-grained security functions. The execute privilege on `DBMS_FGA` is needed for administering audit policies. Because the audit function can potentially capture all user environment and application context values, policy administration should be executable by privileged users only.

This feature is available only for cost-based optimization. The rule-based optimizer may generate unnecessary audit records because audit monitoring can occur before row filtering. For both the rule-based optimizer and the cost-based optimizer, you can refer to `DBA_FGA_AUDIT_TRAIL` to analyze the SQL text and corresponding bind variables that are issued.

The procedures for this package are described in the following subsections:

- ADD_POLICY Procedure

- DISABLE_POLICY Procedure

- DROP_POLICY Procedure

- ENABLE_POLICY Procedure

The syntax, parameters, and usage notes accompanying each procedure description also discuss the defaults and restrictions that apply to it.

### 12.7.1 ADD_POLICY Procedure

This procedure creates an audit policy using the supplied predicate as the audit condition. The maximum number of FGA policies on any table or view object is 256.

#### 12.7.1.1 Syntax

    DBMS_FGA.ADD_POLICY(
       object_schema   VARCHAR2,
       object_name     VARCHAR2,
       policy_name     VARCHAR2,
       audit_condition VARCHAR2,
       audit_column    VARCHAR2,
       handler_schema  VARCHAR2,
       handler_module  VARCHAR2,
       enable          BOOLEAN,
       statement_types VARCHAR2,
       audit_trail     BINARY_INTEGER IN DEFAULT,
       audit_column_opts BINARY_INTEGER IN DEFAULT);

#### 12.7.1.2 Parameters

Table 12-3 ADD_POLICY Procedure Parameters

Parameter | Description | Default Value
--- | --- | ---
`object_schema` | The schema of the object to be audited. (If `NULL`, then the current login user schema is assumed.) | `NULL`
`object_name` | The name of the object to be audited. | -
`policy_name` | The unique name of the policy. | -
`audit_condition` | A condition in a row that indicates a monitoring condition. `NULL` is allowed and acts as `TRUE`. | `NULL`
`audit_column` | The columns to be checked for access. These can include hidden columns. The default, `NULL`, causes audit if any column is accessed or affected. | `NULL`
`handler_schema` | The schema that contains the event handler. The default, NULL, causes the current schema to be used. | `NULL`
`handler_module` | The function name of the event handler includes the package name if necessary. This function is called only after the first row that matches the audit condition in the query is processed. If the procedure fails with an exception, then the user SQL statement will fail as well. | `NULL`
`enable` | Whether the policy is to be enabled: `TRUE` means enable it. | `TRUE`
`statement_types` | The SQL statement types to which this policy is applicable: `INSERT`, `UPDATE`, `DELETE`, or `SELECT` only. | `SELECT`
`audit_trail` | Both where to write the fine-grained audit trail and whether or not to populate `LSQLTEXT` and `LSQLBIND`. | `DB+EXTENDED`
`audit_column_opts` | Whether a statement is audited when the query references any column specified in the `audit_column` parameter or only when all such columns are referenced. | `ANY_COLUMNS`

#### 12.7.1.3 Usage Notes

Usage notes are described as follows:

- Sample command:

      DBMS_FGA.ADD_POLICY(object_schema => 'scott', object_name=>'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types=> 'INSERT, UPDATE', audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ALL_COLUMNS);

- If `object_schema` is not specified, then the current login user schema is assumed.

- An FGA policy should not be applied to out-of-line columns such as LOB columns.

- Each audit policy is applied to the query individually. However, at most one audit record may be generated for each policy, no matter how many returned rows satisfy the `audit_condition` of the policy. In other words, whenever the number of rows returned satisfy an audit condition defined on the table, a single audit record is generated for each such policy.

- If a table with an FGA policy defined on it receives a Fast Path insert or a vectored update, then the hint is automatically disabled before any such operations. Disabling the hint allows auditing to occur according to the policy terms. (One example of a Fast Path insert is the statement `INSERT-WITH-APPEND-hint`.)

- The `audit_condition` must be a Boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. This condition can be `NULL` (or omitted), which is interpreted as `TRUE`, but it cannot contain the following elements:

  - Subqueries or sequences

  - Any direct use of `SYSDATE`, `UID`, `USER` or `USERENV` functions. However, a user-defined function and other SQL functions can use these functions to return the desired information.

  - Any use of the pseudocolumns `LEVEL`, `PRIOR`, or `ROWNUM`.

  Specifying an audit condition of `1=1` to force auditing of all specified statements (`statement_types`) affecting the specified column (`audit_column`) is no longer needed to achieve this purpose. `NULL` will cause audits even if no rows were processed, so that all actions on a table with this policy are audited.

- The audit function (`handler_module`) is an alerting mechanism for the administrator. The required interface for such a function is as follows:

      PROCEDURE <fname> ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 )  AS ...

  where `fname` is the name of the procedure, `object_schema` is the name of the schema of the table audited, `object_name` is the name of the table to be audited, and `policy_name` is the name of the policy being enforced.

- The `audit_trail` parameter specifies both where the fine-grained audit trail will be written and whether the audit trail should include the associated SQL Text and SQL Bind variable information (typically in columns named `LSQLTEXT` and `LSQLBIND`):

  - If `audit_trail` includes XML, then fine-grained audit records are written to XML-format operating system files, irrespective of the `init.ora` `AUDIT_TRAIL` parameter. The XML-format operating system files are stored in the directory specified by an `AUDIT_FILE_DEST` `init.ora` parameter. (The default `AUDIT_FILE_DEST` is `$ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump` on Unix-based systems, and $`ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump` on Windows systems.)

  - If `audit_trail` includes DB instead, then the audit records are written to the `SYS.FGA_LOG$` table in the database.

  - If `audit_trail` includes `EXTENDED`, then the SQL Text and SQL Bind variable information for the query are included in the audit trail.

  - For example:

    - Setting `audit_trail` to `DBMS_FGA.DB` sends the audit trail to the `SYS.FGA_LOG$` table in the database and omits SQL Text and SQL Bind.

    - Setting `audit_trail` to `DBMS_FGA.DB+EXTENDED` sends the audit trail to the `SYS.FGA_LOG$` table in the database and includes SQL Text and SQL Bind.

    - Setting `audit_trail` to `DBMS_FGA.XML` writes the audit trail in XML files sent to the operating system and omits SQL Text and SQL Bind.

    - Setting `audit_trail` to `DBMS_FGA.XML+EXTENDED` writes the audit trail in XML files sent to the operating system and includes SQL Text and SQL Bind.

    Note: When the `init.ora` file contains `AUDIT_TRAIL=XML` (or `XML,EXTENDED`), audit records are written to XML-formatted operating system files.

  The `audit_trail` parameter appears in the `ALL_AUDIT_POLICIES` view.

- You can change the `AUDIT_FILE_DEST`parameter using the following command:

  `ALTER SYSTEM SET AUDIT_FILE_DEST = '<New Directory>' DEFERRED`

- On many platforms, XML audit files are named as follows:

  `<process_name>_<processId>.xml`

  For example, `ora_2111.xml`, or `s002_11.xml`. On Windows, the XML audit files are named `<process_name>_<ThreadId>.xml` (or `<process_name>_ProcessId>.xml` if the process is not running as a thread).

- The `audit_column_opts` parameter establishes whether a statement is audited in any of the following conditions:

  - When the query references any column specified in the audit_column parameter (`audit_column_opts` = `DBMS_FGA.ANY_COLUMNS`)

  - Only when all such columns are referenced (`audit_column_opts` = `DBMS_FGA.ALL_COLUMNS`).

    Note: When `audit_column_opts` is set to `DBMS_FGA.ALL_COLUMNS`, a SQL statement is audited only when all the columns mentioned in `audit_column` have been explicitly referenced in the statement. These columns must have been referenced in the same SQL statement or subquery. Also, all these columns must refer to a single table/view or alias. Thus if a SQL statement selects the columns from different table aliases, then the statement will not be audited.

  The default is `DBMS_FGA.ANY_COLUMNS`.

  The `ALL_AUDIT_POLICIES` view also shows `audit_column_opts`.

#### 12.7.1.4 V$XML_AUDIT_TRAIL View

The new values for the `audit_trail` parameter (`XML` and `XML,EXTENDED`) cause fine-grained auditing records to be written to operating system files in XML format.

Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that DBAs do not have. Operating system storage for audit records also offers higher availability, because such records remain available even if the database is temporarily inaccessible.

A new dynamic view, `V$XML_AUDIT_TRAIL`, makes such audit records from XML files available to DBAs through a SQL query, providing enhanced usability. Querying this view causes all XML files (all files with an `.xml` extension) in the `AUDIT_FILE_DEST` directory to be parsed and presented in relational table format.

The `DBA_COMMON_AUDIT_TRAIL` view includes the contents of the `V$XML_AUDIT_TRAIL` dynamic view for standard and fine-grained audit records.

Because the audit XML files are stored in files with `.xml` extension on all platforms, the dynamic view presents audit information similarly on all platforms, using the following schema:

Table 12-4 Elements in the V$XML_AUDIT_TRAIL Dynamic View

Element | Type
--- | ---
`AUDIT_TYPE` (refer to Note 1.) | `NUMBER`
`CLIENT_ID` | `VARCHAR2(64)`
`COMMENT_TEXT` | `VARCHAR2(4000)`
`DB_USER` | `VARCHAR2(30)`
`ENTRYID` | `NUMBER`
`EXT_NAME` | `VARCHAR2(4000)`
`EXTENDED_TIMESTAMP` (Refer to Note 1.) | `TIMESTAMP(6) WITH TIME ZONE`
`GLOBAL_UID` | `VARCHAR2(32)`
`INSTANCE_NUMBER` | `NUMBER`
`OBJECT_NAME` | `VARCHAR2(128)`
`OBJECT_SCHEMA` | `VARCHAR2(30)`
`OS_PROCESS` (Refer to Note 3.) | `VARCHAR2(16)`
`OS_USER` | `VARCHAR2(255)`
`POLICY_NAME` | `VARCHAR2(30)`
`PROXY_SESSIONID` | `NUMBER` (Refer to Note 3.)
`SCN` | `NUMBER`
`SESSION_ID` | `NUMBER`
`SQL_BIND` (Refer to Note 2.) | `VARCHAR2(4000)`
`SQL_TEXT` (Refer to Note 2.) | `VARCHAR2(4000)`
`STATEMENT_TYPE` | `VARCHAR2(28)`
`STATEMENTID` | `NUMBER`
`TERMINAL` | `VARCHAR2(255)`
`TRANSACTIONID` | `RAW(8)`
`USERHOST` | `VARCHAR2(128)`

Note 1: The `AUDIT_TYPE` column shows 1 for standard XML audit, 2 for fine-grained XML audit, 4 for `SYS` XML audit, and 8 for mandatory XML audit. Every XML audit record contains the elements `Audit_Type` and `Extended_Timestamp`, with the latter printed in UTC zone (with no time zone information). Values retrieved using `V$XML_AUDIT_TRAIL` view are converted to session time zone and printed.

Note 2: For `SQL_TEXT` and `SQL_BIND` element values (CLOB type columns), the dynamic view shows only the first 4000 characters. The underlying XML file may have more than 4000 characters for such `SQL_TEXT` and `SQL_BIND` values.

Note 3: `OS_PROCESS` on Unix systems. In Windows systems, the label is `ProcessId:ThreadId` ( or `ProcessId`, if it is not running as a thread).

Note 4: For a large numbers of XML audit files, querying `V$XML_AUDIT_TRAIL` is faster when they are loaded into a database table using SQL*Loader or a similar tool. XML audit files are larger than the equivalent written to OS files when `AUDIT_TRAIL=OS`.

Error handling is the same as when `AUDIT_TRAIL=OS`. If any error occurs in writing an audit record to disk, including the directory identified by `AUDIT_FILE_DEST` being full, then the auditing operation fails. An alert message is logged.

#### 12.7.1.5 Examples

    DBMS_FGA.ADD_POLICY (object_schema => 'scott', object_name=>'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types=> 'INSERT, UPDATE', audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ALL_COLUMNS);

### 12.7.2 DISABLE_POLICY Procedure

This procedure disables an audit policy.

#### 12.7.2.1 Syntax

    DBMS_FGA.DISABLE_POLICY(
       object_schema  VARCHAR2,
       object_name    VARCHAR2,
       policy_name    VARCHAR2 );

#### 12.7.2.2 Parameters

Table 12-5 DISABLE_POLICY Procedure Parameters

Parameter | Description
--- | ---
`object_schema` | The schema of the object to be audited (If `NULL`, then the current login user schema is assumed.)
`object_name` | The name of the object to be audited
`policy_name` | The unique name of the policy

The default value for `object_schema` is `NULL`. (If `NULL`, then the current login user schema is assumed.)

### 12.7.3 DROP_POLICY Procedure

This procedure drops an audit policy.

#### 12.7.3.1 Syntax

    DBMS_FGA.DROP_POLICY(
       object_schema  VARCHAR2,
       object_name    VARCHAR2,
       policy_name    VARCHAR2 );

#### 12.7.3.2 Parameters

Table 12-6 DROP_POLICY Procedure Parameters

Parameter | Description
--- | ---
`object_schema` | The schema of the object to be audited. (If `NULL`, then the current log-on user schema is assumed.)
`object_name` | The name of the object to be audited.
`policy_name` | The unique name of the policy.

#### 12.7.3.3 Usage Notes

The `DBMS_FGA` procedures cause current DML transactions, if any, to commit before the operation unless they are inside a DDL event trigger. With DDL transactions, the `DBMS_FGA` procedures are part of the DDL transaction. The default value for `object_schema` is `NULL`. (If `NULL`, then the current login user schema is assumed.`)`

### 12.7.4 ENABLE_POLICY Procedure

This procedure enables an audit policy.

#### 12.7.4.1 Syntax

    DBMS_FGA.ENABLE_POLICY(
       object_schema  VARCHAR2,
       object_name    VARCHAR2,
       policy_name    VARCHAR2,
       enable         BOOLEAN);

#### 12.7.4.2 Parameters

Table 12-7 ENABLE_POLICY Procedure Parameters

Parameter | Description
--- | ---
`object_schema` | The schema of the object to be audited. (If `NULL`, then the current log-on user schema is assumed.)
`object_name` | The name of the object to be audited.
`policy_name` | The unique name of the policy.
`enable` | Defaults to `TRUE` to enable the policy.